package com.vecspace.database.dao;

import com.vecspace.constant.DBType;
import com.vecspace.data.model.IPagination;

/**SQL适配类
 * @author：
 */
public class SqlAdapter {
	static{
		//初始化当前数据库标志
	}
	/**创建计数SQL，去掉重复，去掉order by 提高执行效率
	 * @param sql
	 * @return:String
	 * @author：
	 */
	protected static String createCountSQL(String sql)
	{
		if(sql == null || sql.length() == 0) {
			return sql;	
		}
		sql = " " + sql;
		String countSql = "";
		String sqlLower = sql.toLowerCase();
		int firstFromIdx = sqlLower.indexOf(" from ");
		if(firstFromIdx < 0) firstFromIdx = sqlLower.indexOf(" from(");		
		int distinctIdx = sqlLower.indexOf(" distinct");		
		if (distinctIdx>=0 && distinctIdx < firstFromIdx) {
			String str = sql.substring(0, sqlLower.indexOf(" from ") + " form ".length());
			String strLower = str.toLowerCase();//不能改变sql的大小写，因为查询条件里的值，是区分大小写的
			str = str.substring(strLower.lastIndexOf(" distinct") + 8, strLower.lastIndexOf(" from "));
			if (sqlLower.indexOf(" order by ") != -1) {
				countSql = "select count(distinct " + str + ") "
						+ sql.substring(sqlLower.indexOf(" from "), sqlLower.lastIndexOf(" order by "));
			}
			else {
				countSql = "select count(distinct " + str + ") "
						+ sql.substring(sqlLower.indexOf(" from "), sql.length());
			}
		} else if (sqlLower.indexOf(" order by ") != -1) {
			countSql = "select count(*) "
					+ sql.substring(sqlLower.indexOf(" from "), sqlLower.lastIndexOf(" order by "));
		} else {
			countSql = "select count(*) "
					+ sql.substring(sqlLower.indexOf(" from "), sqlLower.length());
		}
		return countSql;
	}
	/**JDBC 分页SQL适配
	 * @param searchBean
	 * @param sql
	 * @return:String
	 * @author：
	 */
    public static String createPageSQL(IPagination pagination, String sql) {
		String pageSQL = "";
		if (pagination.isPaged()) {
			pageSQL = createPageSQL(pagination.getStartRows(), pagination.getEndRows(), sql);
		} else {
			pageSQL = sql;
		}
		return pageSQL;
    }
    
    public static String createPageSQL(String sql, int pageIndex, int pageSize) {
		String pageSQL = "";
		if (pageIndex > 0 && pageSize > 0) {
			int firstRowIndex = (pageIndex - 1) * pageSize;
			int endRowIndex = firstRowIndex + pageSize;
			pageSQL = createPageSQL(firstRowIndex, endRowIndex,	sql);
		} else {
			pageSQL = sql;
		}

		return pageSQL;
 }
    /**创建分页SQL，支持ORACLE和MS SQL Server2005以上
     * 对于MS SQL Server的分页并不完善，请参看{@link SQLServerDialectEx.getLimitString}
     * @param startrow 开始行（从0开始，间隔为pageSize）
     * @param endrow	结束行 startrow+pageSize
     * @param sql 
     * @return 
     * @author 
     */
	public static String createPageSQL(int startrow, int endrow, String sql) {
		String pageSQL = "";
		if (DBType.isMySQLOfCurrent()) {
			int limitRowCount = endrow - startrow;
			pageSQL = sql + " limit " + startrow + "," + limitRowCount; // limit start(页码),pageSize(每页显示的条数)两
		}else if (DBType.isPostgreSQLOfCurrent()) {
			//select * from persons limit  A  offset  B;  A就是你需要多少行；B就是查询的起点位置。A B是bigint类型的值
			//SELECT select_list FROM table_expression [ ORDER BY ... ]	[ LIMIT { number | ALL } ] [ OFFSET number ]
			int pageSize = endrow - startrow;
			String limitSql = " limit " + pageSize + " offset " + pageSQL;
			pageSQL = sql + limitSql;			
		}  
		else if (DBType.isOracleOfCurrent()) { 
			// oracle的rownum 从1开始
			// pageSQL="select * from ( select row_.*, rownum rownum_ from (
			// "+sql+") row_ ) where rownum_ <= "+endrow+" and rownum_ >
			// "+startrow;
			endrow += 1;
			startrow += 1;
			String querySql = "select tmp_tab0_.*, ROWNUM as row_num_ from (" + sql + ") tmp_tab0_ WHERE ROWNUM < "
					+ endrow;
			pageSQL = "SELECT *  FROM (" + querySql + ") tmp_tab2_ WHERE tmp_tab2_.row_num_ >= " + startrow;
		} else if (DBType.isMSSQLOfCurrent()) {
			pageSQL = SQLServerDialectEx.getLimitString(startrow + 1, endrow, sql);
		} else if (DBType.isSqliteOfCurrent()) {
			int limitRowCount = endrow - startrow;
			pageSQL = sql + " limit " + limitRowCount + " offset " + startrow;
		} else if (DBType.isDerbyOfCurrent()) {// SELECT * FROM YOUR_TABLE OFFSET ? ROWS FETCH NEXT ? ROWS ONLY;
			int limitRowCount = endrow - startrow;
			pageSQL = sql + " OFFSET " + startrow + " ROWS FETCH NEXT  " + limitRowCount + " ROWS ONLY"; //
		}
		return pageSQL;
	}
    
    /**获取转换为日期的SQL（不同数据库语法不同）
     * @param date
     * @param format
     * @return 
     * @author 
     */
    public static String getToDateSQL(String date,String format){
    	String result="";
	
		if(DBType.isMySQLOfCurrent())
		{	
			result="str_to_date('"+date+"','"+format+"')";	
		}else if(DBType.isPostgreSQLOfCurrent())
		{	
			if(format.length() > 12) {
				result="TO_DATE('"+date+"','"+format+"')";	
			}else {
				result="TO_TIMESTAMP('"+date+"','"+format+"')";	
			}			
		}else if(DBType.isOracleOfCurrent())
		{	
			//TO_DATE('"+sdf.format(fromTime)+"','yyyy-mm-dd hh24:mi:ss')
			result="TO_DATE('"+date+"','"+format+"')";	
		}else if(DBType.isMSSQLOfCurrent())
		{
			if (format.equalsIgnoreCase("yyyy-mm-dd hh24:mi:ss")){
				//CONVERT(datetime,'2012-10-11 22:10:11',20)
				result="CONVERT(datetime,'"+date+"',20)";	
	    	}else if(format.equalsIgnoreCase("yyyy-mm-dd")){
	    		//result="CONVERT(datetime,'"+date+" 00:00:00',20)";
	    		result="cast('"+date+"',datetime)";
	    	}
		}
    	return result;
    	
    }
    
    /**返回不同数据库下的字符串拼接sql
     * @param columnNames 字段名，或者固定字符串，如果是固定字符串，需要用''引起来， 比如 :getConcatExp("F1","'-'","F2")，在mysql下返回"CONCAT(F1,'-',F2)"
     * @return
     */
    public static String getConcatExp(String... columnNames) {    	
    	String concatexp = "";
    	if(DBType.isOracleOfCurrent())
		{	
    		concatexp = "NVL("+ columnNames[0] +",'')";
    		for(int i=1; i< columnNames.length; ++i) {
    			concatexp = concatexp + " || " +  "NVL("+ columnNames[i] +",'')";
    		}
		}else if(DBType.isPostgreSQLOfCurrent())
		{	
			concatexp = columnNames[0];
			for(int i=1; i< columnNames.length; ++i) {
    			concatexp = concatexp + " , " + columnNames[i];
    		}
    		concatexp = "CONCAT("+concatexp+")"; //concat,连接函数里所有的字符串参数，忽略空字符串
		}else if(DBType.isMySQLOfCurrent())
		{	
			concatexp = "ifnull("+ columnNames[0] +",'')";
    		for(int i=1; i< columnNames.length; ++i) {
    			concatexp = concatexp + " , " + "ifnull("+ columnNames[i] +",'')";
    		}
    		
    		concatexp = "CONCAT("+concatexp+")";
		}
    	return concatexp;
    	
    }
    
}
